C#执行SQLServer的增删改查

2024-07-16 13:33| 来源: 网络整理| 查看: 265

class Program { #region 示例1：正确打开和关闭数据库的连接 static void Main(string[] args) { //编写连接字符串 string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;pwd=sasa"; //创建连接对象 SqlConnection conn = new SqlConnection(conString); //SqlConnection conn = new SqlConnection(); //conn.ConnectionString = conString; //打开连接 conn.Open(); //判断连接是否打开 if (conn.State == ConnectionState.Open) { Console.WriteLine("Connection is opened!"); } //关闭连接 conn.Close(); if (conn.State == ConnectionState.Closed) { Console.WriteLine("Connection is closed!"); } Console.ReadLine(); } #endregion #region 示例2：添加学员对象 static void Main(string[] args) { //编写连接字符串 string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=sasa"; //创建连接对象 SqlConnection conn = new SqlConnection(conString); //组合SQL语句 string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,"; sql += "PhoneNumber,StudentAddress,ClassId)"; sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})"; sql = string.Format(sql, "王小路", "男", "1990-09-18", 120226199009181518, 23, "022-89895566", "天津市静海县", 2); //创建Command对象 //SqlCommand cmd = new SqlCommand(); //cmd.CommandText = sql; //cmd.Connection = conn; SqlCommand cmd = new SqlCommand(sql, conn); //打开连接 conn.Open(); //执行操作 int result = cmd.ExecuteNonQuery(); //及时关闭连接 conn.Close(); if (result == 1) Console.WriteLine("插入成功！"); else Console.WriteLine("插入失败！"); Console.ReadLine(); } #endregion #region 示例3：修改学员对象 static void Main(string[] args) { //编写连接字符串 string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=sasa"; //创建连接对象 SqlConnection conn = new SqlConnection(conString); //组合SQL语句 string sql = "update Students set StudentName='{0}' where StudentId={1}"; sql = string.Format(sql, "王小路", 100009); //创建Command对象 SqlCommand cmd = new SqlCommand(sql, conn); //打开连接 conn.Open(); //执行操作 int result = cmd.ExecuteNonQuery();//执行操作 //及时关闭连接 conn.Close(); if (result == 1) Console.WriteLine("修改成功！"); else Console.WriteLine("修改失败！"); Console.ReadLine(); } #endregion #region 示例4：删除学员对象 static void Main(string[] args) { //编写连接字符串 string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=sasa"; //创建连接对象 SqlConnection conn = new SqlConnection(conString); //组合SQL语句 string sql = "delete from Students where StudentId=" + 100010; //创建Command对象 SqlCommand cmd = new SqlCommand(sql, conn); //打开连接 conn.Open(); //执行操作 int result = cmd.ExecuteNonQuery();//执行操作 //及时关闭连接 conn.Close(); if (result == 1) Console.WriteLine("删除成功！"); else Console.WriteLine("删除失败！"); Console.ReadLine(); } #endregion #region 示例5：多条更新的SQL语句执行方法使用ExcuteNonQuery方法同时执行多条SQL语句 static void Main(string[] args) { string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=password01!"; SqlConnection conn = new SqlConnection(conString); //组合SQL语句 string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,"; sql += "Age,PhoneNumber,StudentAddress,ClassId)"; sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})"; string sql1 = string.Format(sql, "王小路", "男", "1990-09-18", 120226199009181518, 23, "022-89895566", "天津市静海县", 2); string sql2 = string.Format(sql, "王小刚", "男", "1992-04-15", 120226199204151518, 23, "022-89895566", "天津市河西区", 1); string sql3 = "update Students set StudentName='{0}' where StudentId={1}"; sql3 = string.Format(sql3, "王小美", 100009); //将不同的SQL语句使用分号连接在一起 string manySql = sql1 + ";" + sql2 + ";" + sql3; SqlCommand cmd = new SqlCommand(manySql, conn); conn.Open(); int result = cmd.ExecuteNonQuery();//执行操作 conn.Close(); if (result == 3) Console.WriteLine("操作成功！"); else Console.WriteLine("操作失败！"); Console.ReadLine(); } #endregion #region 示例6：插入一个学员对象并返回标识列 static void Main(string[] args) { string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=password01!"; SqlConnection conn = new SqlConnection(connString); //组合SQL语句，要求同时执行insert和select string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)"; sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7});select @@identity"; sql = string.Format(sql, "李大国", "男", "1993-05-18", 120226199305181518, 23, "022-89895566", "天津市红桥区", 3); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); object newStuId = cmd.ExecuteScalar(); //使用Scalar方法同时执行insert和select操作 conn.Close(); Console.WriteLine("新增加学员的学号：{0}", newStuId); Console.ReadLine(); } #endregion

基础知识：

普通属性：可读或可写并将值存储到一个私有变量中的属性，不对数据做任何加工，没有自定义代码。

private string name public string Name { get{return name} set{name = value} } 快速生成键入“propfull”连按两次“Tab”，自动生成，再修改变量名和属性名

自动属性（代替普通属性）

public string Name {get;set;} 快速生成键入“prop”连按两次“Tab”，自动生成

取值方法和赋值方法指定不同的访问权限，依然可以。

快速生成键入“propg”连续两次“Tab”，自动生成公开读，私有写的属性。

【本文地址】

公司简介

联系我们